/**
 * 
 */
package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.model.MRefList;
import org.compiere.model.Mhrmabsence;
import org.compiere.model.Mhrmemployee;
import org.compiere.model.X_hrm_salaryemployee;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.report.ReportPayroll.PayrollList;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHREmployee;
import org.eevolution.model.MHRJob;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

/**
 * @author A.Kulantayev
 */
public class FI_Form_T1 extends SvrProcess {
	//FI_Form_T1_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	/** */
	private Timestamp p_DateAcct_From = null;
	private Timestamp p_DateAcct_To = null;
	/**Current Record_ID */
	private int p_Record_ID = 0;	
	/**Out file */
	String ResultPath;

	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("DateAcct")) {
				p_DateAcct_From = (Timestamp) para[i].getParameter();
				p_DateAcct_To = (Timestamp) para[i].getParameter_To();
			}
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
	}

	/* (non-Javadoc)
	 * @see org.compiere.process.SvrProcess#doIt()
	 */
	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = Util.getResultPath("FI_Form_T1_", 12);
		// 
		  MAttachmentEntry entry = Util. getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		XSSFWorkbook wb = new XSSFWorkbook(entry.getInputStream());
		XSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		//Report Header
		MBPartner ad_Org_BP = new MBPartner(m_ctx, new MOrg(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getLinkedC_BPartner_ID(get_TrxName()), get_TrxName());
		sheet.getRow(7).getCell(42).setCellValue(ad_Org_BP.getFI_BIN_IIN());
		sheet.getRow(9).getCell(23).setCellValue(new MOrg(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getName());
		String period="";
		period ="с "+ new SimpleDateFormat("dd.MM.yyyy").format(p_DateAcct_From).toString()
				+"  по "+new SimpleDateFormat("dd.MM.yyyy").format(p_DateAcct_To).toString()+" года";
		sheet.getRow(11).getCell(23).setCellValue(period);
		//
		int rowNo = 19;
		PayrollList[] lines =  getValues();
		
		for(int i = 0, no=1; i< lines.length; i++, no++,rowNo++)
		{
			sheet.shiftRows(rowNo, rowNo+1, 1);
			R = sheet.createRow(rowNo);
			for(int j=0;j<192;j++)
			{
				R.createCell(j).setCellStyle(sheet.getRow(18).getCell(191).getCellStyle());
				R.getCell(0).setCellStyle(sheet.getRow(18).getCell(0).getCellStyle());
			}
			//col1
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 0, 2));
			R.getCell(0).setCellValue(String.valueOf(no));
			//col2
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 3, 6));
			MBPartner mbPartner = new MBPartner(m_ctx, lines[i].getBPartner_ID(), get_TrxName());
			R.getCell(3).setCellValue(mbPartner.gethrm_tableNumber());
			//col3
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 7, 18));
			R.getCell(7).setCellStyle(sheet.getRow(1).getCell(0).getCellStyle());
			R.getCell(7).setCellValue(mbPartner.getName());
			//col4
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 19, 24));
			//col5
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 25, 30));
			MHRJob mhrJob = new MHRJob(m_ctx, lines[i].getHR_Job_ID(), get_TrxName());
			if(mhrJob!=null){
				R.getCell(25).setCellStyle(sheet.getRow(1).getCell(0).getCellStyle());
				R.getCell(25).setCellValue(mhrJob.getName());
			}
			//col6
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 31, 36));
			//col7
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 37, 42));
			R.getCell(37).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			R.getCell(37).setCellValue(lines[i].getSalary());
			//col8
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 43, 47));
			R.getCell(43).setCellValue(lines[i].getWageRate());
			//col9
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 48, 50));
			R.getCell(48).setCellStyle(sheet.getRow(2).getCell(0).getCellStyle());
			R.getCell(48).setCellValue(lines[i].getWorkDays());
			//col10
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 51, 53));
			R.getCell(51).setCellStyle(sheet.getRow(2).getCell(0).getCellStyle());
			R.getCell(51).setCellValue(lines[i].getWorkHours());
			//col11
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 54, 55));
			R.getCell(54).setCellStyle(sheet.getRow(2).getCell(0).getCellStyle());
			R.getCell(54).setCellValue(lines[i].getWorkHours());
			//col12
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 56, 59));
			R.getCell(56).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			R.getCell(56).setCellValue(lines[i].getAmount());
			//col13
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 60, 62));
			//col14
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 63, 65));
			R.getCell(63).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			//col15
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 66, 68));
			//col16
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 69, 71));
			R.getCell(69).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			//col17
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 72, 74));
			//col18
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 75, 77));
			R.getCell(75).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			//col19
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 78, 83));
			R.getCell(78).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			//col20
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 84, 86));
			//col21
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 87, 89));
			R.getCell(87).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			//col22
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 90, 92));
			//col23
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 93, 95));
			R.getCell(93).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			//col24
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 96, 97));
			//col25
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 98, 100));
			Mhrmemployee[] bonuses = getBonuses(lines[i].getBPartner_ID(), p_DateAcct_From, p_DateAcct_To);
			double percentBonusSum = 0;
			if(bonuses.length!=0){
				for(int b=0;b<bonuses.length;b++){
					percentBonusSum+=bonuses[b].gethrm_percentBonus().doubleValue();
				}
				R.getCell(98).setCellStyle(sheet.getRow(3).getCell(0).getCellStyle());
				R.getCell(98).setCellValue(percentBonusSum/100);
			}
			
			//col26
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 101, 104));
			R.getCell(101).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			if(lines[i].getBonus()>0)
			R.getCell(101).setCellValue(lines[i].getBonus());
			//col27
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 105, 107));
			Mhrmabsence[] mhrmabsencesIllness = Mhrmabsence.getAbsenceType(lines[i].getBPartner_ID(), p_DateAcct_From, p_DateAcct_To, Mhrmabsence.HRM_ABSENCETYPE_Illness);
			StringBuffer strIllnes = new StringBuffer();
			for(int l=0; l<mhrmabsencesIllness.length;l++)
			{
				if(l>0)
					strIllnes.append(", ");
				strIllnes.append("с ");
				strIllnes.append(new SimpleDateFormat("dd.MM.yy").format(mhrmabsencesIllness[l].getStartDate()));
				strIllnes.append(" по ");
				strIllnes.append(new SimpleDateFormat("dd.MM.yy").format(mhrmabsencesIllness[l].getEndDate()));
			}
			R.getCell(105).setCellValue(strIllnes.toString());
			//col28
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 108, 110));
			R.getCell(108).setCellStyle(sheet.getRow(2).getCell(0).getCellStyle());
			if(lines[i].getIll_Days()>0)
			R.getCell(108).setCellValue(lines[i].getIll_Days());
			//col29
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 111, 114));
			R.getCell(111).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			if(lines[i].getIllness()>0)
			R.getCell(111).setCellValue(lines[i].getIllness());
			//col30
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 115, 118));
			R.getCell(115).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			if(lines[i].getProfit()>0)
			R.getCell(115).setCellValue(lines[i].getProfit());
			//col31
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 119, 123));
			R.getCell(119).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			if(lines[i].getTotalSum()>0)
			R.getCell(119).setCellValue(lines[i].getTotalSum());
			//col32
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 124, 127));
			R.getCell(124).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			if(lines[i].getInter()>0)
			R.getCell(124).setCellValue(lines[i].getInter());
			//col33
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 128, 131));
			R.getCell(128).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			String sqlTotalSum = "SELECT totalsum FROM HRM_SalarySum_V WHERE 1=? AND C_BPartner_ID = "+lines[i].getBPartner_ID()+" AND DateFrom = DATE_TRUNC('MONTH',"+DB.TO_DATE(p_DateAcct_To, true)+")";
			BigDecimal currMonthTotalSum = new BigDecimal(0);
			currMonthTotalSum = DB.getSQLValueBD(get_TrxName(), sqlTotalSum, 1);
			if(currMonthTotalSum!=null)
				R.getCell(128).setCellValue(currMonthTotalSum.doubleValue());
				
			//col34
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 132, 135));
			R.getCell(132).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			if(lines[i].getTotalSum()>0)
				R.getCell(132).setCellValue(lines[i].getTotalSum());
			//col35
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 136, 139));
			R.getCell(136).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			String sqlIPN = "SELECT ipn FROM HRM_SalarySum_V WHERE 1=? AND C_BPartner_ID = "+lines[i].getBPartner_ID()+" AND DateFrom = DATE_TRUNC('MONTH',"+DB.TO_DATE(p_DateAcct_To, true)+")";
			BigDecimal currMonthIPN = new BigDecimal(0);
			currMonthIPN = DB.getSQLValueBD(get_TrxName(), sqlIPN, 1);
			if(currMonthIPN!=null)
				R.getCell(136).setCellValue(currMonthIPN.doubleValue());
			//col36
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 140, 143));
			R.getCell(140).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			if(lines[i].getIPN()>0)
				R.getCell(140).setCellValue(lines[i].getIPN());
			//col37
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 144, 147));
			R.getCell(144).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			BigDecimal currMonthOPV = new BigDecimal(0);
			String sqlOPV = "SELECT opv FROM HRM_SalarySum_V WHERE 1=? AND C_BPartner_ID = "+lines[i].getBPartner_ID()+" AND DateFrom = DATE_TRUNC('MONTH',"+DB.TO_DATE(p_DateAcct_To, true)+")";
			currMonthOPV = DB.getSQLValueBD(get_TrxName(), sqlOPV, 1);
			if(currMonthOPV!=null)
				R.getCell(144).setCellValue(currMonthOPV.doubleValue());
			//col38
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 148, 151));
			R.getCell(148).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			if(lines[i].getOPV()>0)
				R.getCell(148).setCellValue(lines[i].getOPV());
			//col39
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 152, 155));
			R.getCell(152).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			if(lines[i].getCheckOff()>0)
				R.getCell(152).setCellValue(lines[i].getCheckOff());
			//col40
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 156, 158));
			//col41
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 159, 162));
			R.getCell(159).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			if(lines[i].getCheckOff()>0)
				R.getCell(159).setCellValue(lines[i].getCheckOff());
			//col42
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 163, 166));
			R.getCell(163).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			if(lines[i].getTotalOff()>0)
			R.getCell(163).setCellValue(lines[i].getTotalOff());
			//col43
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 167, 170));
			R.getCell(167).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			if(lines[i].getLoan()>0)
				R.getCell(167).setCellValue(lines[i].getLoan());
			//col44
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 171, 174));
			R.getCell(171).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			if(lines[i].getAmount()>0)
			R.getCell(171).setCellValue(lines[i].getAmount());
			//col45
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 175, 178));
			R.getCell(175).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
			if(lines[i].getResult()>0)
			R.getCell(175).setCellValue(lines[i].getResult());
			//col46
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 179, 181));
			MHREmployee mhrEmployee = new MHREmployee(m_ctx, lines[i].getHR_Employee_ID(), get_TrxName());
			R.getCell(179).setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(mhrEmployee.gethrm_dateOfBirth()));
			//col47
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 182, 184));
			String sql = "SELECT AD_Ref_List_ID FROM AD_Ref_List WHERE AD_Reference_ID = 1000014 " +
					"AND Value = (SELECT hrm_maritalstatus FROM hrm_familydescription WHERE C_BPartner_ID = " +mbPartner.getC_BPartner_ID()+
								  " AND Updated = (SELECT MAX(Updated) FROM hrm_familydescription WHERE C_BPartner_ID = "+mbPartner.getC_BPartner_ID()+"))";
			MRefList mRefList = new MRefList(m_ctx, DB.getSQLValue(get_TrxName(), sql), get_TrxName());
			if(mRefList!=null)
			R.getCell(182).setCellValue(mRefList.getName());
			//col48
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 185, 188));
			Mhrmabsence[] mhrmabsencesVac = Mhrmabsence.getAbsenceType(mbPartner.getC_BPartner_ID(), p_DateAcct_From, p_DateAcct_To, Mhrmabsence.HRM_ABSENCETYPE_Vacation);
			StringBuffer str= new StringBuffer();
			for(int k=0; k<mhrmabsencesVac.length;k++){
				if(k>0)
					str.append(",");
				str.append(" c ");
				str.append(new SimpleDateFormat("dd.MM.yyyy").format(mhrmabsencesVac[k].getStartDate()).toString());
				str.append(" по ");
				str.append(new SimpleDateFormat("dd.MM.yyyy").format(mhrmabsencesVac[k].getEndDate()).toString());
			}
			if (strIllnes!=null)
			R.getCell(185).setCellValue(str.toString());
			//col49
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 189, 191));
		}
		//All Totals
		R = sheet.getRow(rowNo);
		//col3
		R.getCell(0).setCellStyle(sheet.getRow(1).getCell(0).getCellStyle());
		R.getCell(0).setCellValue("Итого");
		//col7
		R.getCell(37).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(37).setCellFormula("SUM(AL19:AL" + rowNo + ")");
		//col9
		R.getCell(48).setCellStyle(sheet.getRow(2).getCell(0).getCellStyle());
		R.getCell(48).setCellFormula("SUM(AW19:AW" + rowNo + ")");
		//col10
		R.getCell(51).setCellStyle(sheet.getRow(2).getCell(0).getCellStyle());
		R.getCell(51).setCellFormula("SUM(AZ19:AZ" + rowNo + ")");
		//col11
		R.getCell(54).setCellStyle(sheet.getRow(2).getCell(0).getCellStyle());
		R.getCell(54).setCellFormula("SUM(BC19:BC" + rowNo + ")");
		//col12
		R.getCell(56).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(56).setCellFormula("SUM(BE19:BE" + rowNo + ")");
		//col13
		//col14
		//col15
		//col16
		//col17
		//col18
		//col19
		//col20
		//col21
		//col22
		//col23
		//col25
		R.getCell(98).setCellStyle(sheet.getRow(3).getCell(0).getCellStyle());
		R.getCell(98).setCellFormula("SUM(CU19:CU" + rowNo + ")");
		//col26
		R.getCell(101).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(101).setCellFormula("SUM(CX19:CX" + rowNo + ")");
		//col28
		R.getCell(108).setCellStyle(sheet.getRow(2).getCell(0).getCellStyle());
		R.getCell(108).setCellFormula("SUM(DE19:DE" + rowNo + ")");
		//col29
		R.getCell(111).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(111).setCellFormula("SUM(DH19:DH" + rowNo + ")");
		//col30
		R.getCell(115).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(115).setCellFormula("SUM(DL19:DL" + rowNo + ")");
		//col31
		R.getCell(119).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(119).setCellFormula("SUM(DP19:DP" + rowNo + ")");
		//col32
		R.getCell(124).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(124).setCellFormula("SUM(DU19:DU" + rowNo + ")");
		//col33
		R.getCell(128).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(128).setCellFormula("SUM(DY19:DY" + rowNo + ")");
		//col34
		R.getCell(132).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(132).setCellFormula("SUM(EC19:EC" + rowNo + ")");
		//col35
		R.getCell(136).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(136).setCellFormula("SUM(EG19:EG" + rowNo + ")");
		//col36
		R.getCell(140).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(140).setCellFormula("SUM(EK19:EK" + rowNo + ")");
		//col37
		R.getCell(144).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(144).setCellFormula("SUM(EO19:EO" + rowNo + ")");
		//col38
		R.getCell(148).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(148).setCellFormula("SUM(ES19:ES" + rowNo + ")");
		//col39
		R.getCell(152).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(152).setCellFormula("SUM(EW19:EW" + rowNo + ")");
		//col41
		R.getCell(159).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(159).setCellFormula("SUM(FD19:FD" + rowNo + ")");
		//col42
		R.getCell(163).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(163).setCellFormula("SUM(FH19:FH" + rowNo + ")");
		//col43
		R.getCell(167).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(167).setCellFormula("SUM(FL19:FL" + rowNo + ")");
		//col44
		R.getCell(171).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(171).setCellFormula("SUM(FP19:FP" + rowNo + ")");
		//col45
		R.getCell(175).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		R.getCell(175).setCellFormula("SUM(FT19:FT" + rowNo + ")");
		//
		
		//Delete Temp CellStyles
		sheet.getRow(0).getCell(0).setCellStyle(sheet.getRow(0).getCell(47).getCellStyle());
		sheet.getRow(1).getCell(0).setCellStyle(sheet.getRow(0).getCell(47).getCellStyle());
		sheet.getRow(2).getCell(0).setCellStyle(sheet.getRow(0).getCell(47).getCellStyle());
		sheet.getRow(3).getCell(0).setCellStyle(sheet.getRow(0).getCell(47).getCellStyle());
		//
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
		Util.launchFile(new File(ResultPath));	
		return Msg.translate(m_ctx, "Success");
	}
	private Mhrmemployee[] getBonuses(int C_BPartner_ID, Timestamp sdt, Timestamp edt){
		List<Mhrmemployee> list = new ArrayList<Mhrmemployee>();
		Mhrmemployee bonuses = null;
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT HRM_Employee_ID "+ 
					 "\nFROM HRM_Employee "+ 
				     "\nWHERE HRM_Order_ID IN "+
				     "\n(SELECT HRM_Order_ID "+ 
					 "\nFROM HRM_Order "+ 
					 "\nWHERE HRM_OrderType = '7' "+ 
					 "\nAND Processing='Y' "+ 
					 "\nAND DateFrom BETWEEN "+DB.TO_DATE(sdt, false)+" AND "+DB.TO_DATE(edt, false)+") "+ 
					 "\nAND AD_Client_ID = "+Env.getAD_Client_ID(m_ctx)+ 
					 "\nAND AD_Org_ID = "+Env.getAD_Org_ID(m_ctx)+ 
					 "\nAND Processed='Y' "+ 
					 "\nAND C_BPartner_ID = "+C_BPartner_ID;
		try{
			pstmt = DB.prepareStatement(sql, get_TrxName());
			rs = pstmt.executeQuery();
			while(rs.next()){			
				bonuses = new Mhrmemployee(m_ctx, rs.getInt(1), get_TrxName());
				list.add(bonuses);
				bonuses = null;
			}
			
		}catch(Exception e){
			log.log(Level.SEVERE, sql, e);
		}finally{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		return list.toArray(new Mhrmemployee[list.size()]);
	}
	
	
	private PayrollList[] getValues(){
		
		List<PayrollList> list = new ArrayList<PayrollList>();
		PayrollList values = new PayrollList();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		try
		{
			pstmt = DB.prepareStatement(getSql(),null);
			rs = pstmt.executeQuery();	
					
			while (rs.next ())
			{
				values = new PayrollList();
				values.setBPartner_ID(rs.getInt("c_bpartner_id"));
				values.setHR_Employee_ID(rs.getInt("hr_employee_id"));
				values.setHR_Job_ID(rs.getInt("hr_job_id"));
				values.setSalary(rs.getDouble("salary"));
				values.setWorkDays(rs.getInt("workdays"));
				values.setWorkHours(rs.getInt("workhours"));
				values.setWageRate(rs.getString("wagerate"));
				values.setBonus(rs.getDouble("bonus"));
				values.setTotalSum(rs.getDouble("totalsum"));
				values.setIPN(rs.getDouble("ipn"));
				values.setOPV(rs.getDouble("opv"));
				values.setTotalOff(rs.getDouble("totaloff"));
				values.setResult(rs.getDouble("result"));
				values.setLoan(rs.getDouble("loan"));
				values.setVacation(rs.getDouble("vacation"));
				values.setVac_Days(rs.getInt("vac_days"));
				values.setAidVacation(rs.getDouble("aidvacation"));
				values.setIllness(rs.getDouble("illness"));
				values.setIll_Days(rs.getInt("ill_days"));
				values.setMaterialaid(rs.getDouble("materialaid"));
				values.setMaterialaidtaxOff(rs.getDouble("materialaidtaxoff"));
				values.setMedicare(rs.getDouble("medicare"));
				values.setMediare_amount(rs.getDouble("medicare_amount"));
				values.setProfit(rs.getDouble("profit"));
				values.setProfit_amount(rs.getDouble("profit_amount"));
				values.setCheckOff(rs.getDouble("checkoff"));
				values.setInter(rs.getDouble("inter"));
				values.setReview(rs.getDouble("review"));
				values.setReview_days(rs.getInt("review_days"));
				values.setOvertime(rs.getDouble("overtime"));
				values.setOvertime_days(rs.getInt("overtime_days"));
				values.setBtrip(rs.getDouble("btrip"));
				values.setBtriptax(rs.getDouble("btriptax"));
				values.setCotch(rs.getDouble("cotch"));
				values.setCnalog(rs.getDouble("cnalog"));
				values.setRelief(rs.getDouble("relief"));
				values.setTotaldeduction(rs.getDouble("totaldeduction"));
				values.setAmount(rs.getDouble("amount"));
				list.add(values);
			}
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return list.toArray(new PayrollList[list.size()]);
	}
	
	private String getSql(){
		
		String sql = "SELECT 'ok'";
		sql = "SELECT t.C_BPartner_ID "+
		     "\n, r.HR_Employee_ID "+
			 "\n, r.HR_Job_ID "+
			 "\n, MAX(t.Salary) AS Salary "+
			 "\n, SUM(t.WorkDays) AS WorkDays "+
			 "\n, SUM(t.WorkHours) AS WorkHours "+
			 "\n, MAX('8 часовая') AS WageRate "+
			 "\n, SUM(t.Bonus) AS Bonus "+
			 "\n, SUM(t.TotalSum) AS TotalSum "+
			 "\n, SUM(t.ipn) AS ipn "+
			 "\n, SUM(t.opv) AS opv "+
			 "\n, SUM(t.totaloff) AS totalOff "+
			 "\n, SUM(t.Result) AS Result "+
			 "\n, SUM(t.loan) AS Loan "+
			 "\n, SUM(t.vacation) AS Vacation "+
			 "\n, SUM(t.vac_days) AS vac_days "+
			 "\n, SUM(t.aidvacation) AS aidvacation "+
			 "\n, SUM(t.illness) AS illness "+
			 "\n, SUM(t.ill_days) AS ill_days "+
			 "\n, SUM(t.materialaid) AS materialaid "+
			 "\n, SUM(t.materialaidtaxoff) AS materialaidtaxoff "+
			 "\n, SUM(t.medicare) AS medicare "+
			 "\n, SUM(t.medicare_amount) AS medicare_amount "+
			 "\n, SUM(t.profit) AS profit "+
			 "\n, SUM(t.profit_amount) AS profit_amount "+
			 "\n, SUM(t.checkoff) AS checkoff "+
			 "\n, SUM(t.inter) AS inter "+
			 "\n, SUM(t.review) AS review "+
			 "\n, SUM(t.review_days) AS review_days "+
			 "\n, SUM(t.overtime) AS overtime "+
			 "\n, SUM(t.overtime_days) AS overtime_days "+
			 "\n, SUM(t.btrip) AS btrip "+
			 "\n, SUM(t.btriptax) AS btriptax "+
			 "\n, SUM(t.cotch) AS cotch "+
			 "\n, SUM(t.cnalog) AS cnalog "+
			 "\n, SUM(t.relief) AS relief "+
			 "\n, SUM(t.totaldeduction) AS totaldeduction "+ 
			 "\n, SUM(t.amount) AS Amount "+
			 "\nFROM HRM_SalarySum_V t "+  
			 "\nINNER JOIN HR_Employee r ON r.C_BPartner_ID=t.C_BPartner_ID " +
			 "\nAND R.EndDate = (SELECT MAX(H.EndDate) FROM HR_Employee h WHERE H.C_BPartner_ID=r.C_BPartner_ID) "+ 
			 "\nWHERE t.AD_Client_ID = "+ Env.getAD_Client_ID(m_ctx)+
			 "\nAND t.AD_Org_ID = "+Env.getAD_Org_ID(m_ctx)+
			 "\nAND t.DateFrom BETWEEN "+DB.TO_DATE(p_DateAcct_From, false) +" AND "+DB.TO_DATE(p_DateAcct_To, false)+		  
			 "\nGROUP BY t.C_BPartner_ID, r.HR_Job_ID, r.HR_Employee_ID "+
			 "\nORDER BY (SELECT Name FROM C_BPartner WHERE t.C_BPartner_ID = C_BPartner.C_BPartner_ID) "; 
			
		return sql.toString();
	}	
	
	public class PayrollList {
		
		private int BPartner_ID;
		private int HR_Employee_ID;
		private int HR_Job_ID;
		private double salary;
		private int workDays;
		private int workHours;
		private String wageRate;
		private double bonus;
		private double totalSum;
		private double IPN;
		private double OPV;
		private double totalOff;
		private double result;
		private double loan;
		private double vacation;
		private int vac_Days;
		private double aidVacation;
		private double illness;
		private int ill_Days;
		private double materialaid;
		private double materialaidtaxOff;
		private double medicare;
		private double mediare_amount;
		private double profit;
		private double profit_amount;
		private double checkOff;
		private double inter;
		private double review;
		private int review_days;
		private double overtime;
		private int overtime_days;
		private double btrip;
		private double btriptax;
		private double cotch;
		private double cnalog;
		private double relief;
		private double totaldeduction;
		private double amount;
		
		public int getBPartner_ID() {
			return BPartner_ID;
		}
		public void setBPartner_ID(int bPartner_ID) {
			BPartner_ID = bPartner_ID;
		}
		public int getHR_Employee_ID() {
			return HR_Employee_ID;
		}
		public void setHR_Employee_ID(int hR_Employee_ID) {
			HR_Employee_ID = hR_Employee_ID;
		}
		public int getHR_Job_ID() {
			return HR_Job_ID;
		}
		public void setHR_Job_ID(int hR_Job_ID) {
			HR_Job_ID = hR_Job_ID;
		}
		public double getSalary() {
			return salary;
		}
		public void setSalary(double salary) {
			this.salary = salary;
		}
		public int getWorkDays() {
			return workDays;
		}
		public void setWorkDays(int workDays) {
			this.workDays = workDays;
		}
		public int getWorkHours() {
			return workHours;
		}
		public void setWorkHours(int workHours) {
			this.workHours = workHours;
		}
		public String getWageRate() {
			return wageRate;
		}
		public void setWageRate(String wageRate) {
			this.wageRate = wageRate;
		}
		public double getBonus() {
			return bonus;
		}
		public void setBonus(double bonus) {
			this.bonus = bonus;
		}
		public double getTotalSum() {
			return totalSum;
		}
		public void setTotalSum(double totalSum) {
			this.totalSum = totalSum;
		}
		public double getIPN() {
			return IPN;
		}
		public void setIPN(double iPN) {
			IPN = iPN;
		}
		public double getOPV() {
			return OPV;
		}
		public void setOPV(double oPV) {
			OPV = oPV;
		}
		public double getTotalOff() {
			return totalOff;
		}
		public void setTotalOff(double totalOff) {
			this.totalOff = totalOff;
		}
		public double getResult() {
			return result;
		}
		public void setResult(double result) {
			this.result = result;
		}
		public double getLoan() {
			return loan;
		}
		public void setLoan(double loan) {
			this.loan = loan;
		}
		public double getVacation() {
			return vacation;
		}
		public void setVacation(double vacation) {
			this.vacation = vacation;
		}
		public double getVac_Days() {
			return vac_Days;
		}
		public void setVac_Days(int vac_Days) {
			this.vac_Days = vac_Days;
		}
		public double getAidVacation() {
			return aidVacation;
		}
		public void setAidVacation(double aidVacation) {
			this.aidVacation = aidVacation;
		}
		public double getIllness() {
			return illness;
		}
		public void setIllness(double illness) {
			this.illness = illness;
		}
		public int getIll_Days() {
			return ill_Days;
		}
		public void setIll_Days(int ill_Days) {
			this.ill_Days = ill_Days;
		}
		public double getMaterialaid() {
			return materialaid;
		}
		public void setMaterialaid(double materialaid) {
			this.materialaid = materialaid;
		}
		public double getMaterialaidtaxOff() {
			return materialaidtaxOff;
		}
		public void setMaterialaidtaxOff(double materialaidtaxOff) {
			this.materialaidtaxOff = materialaidtaxOff;
		}
		public double getMedicare() {
			return medicare;
		}
		public void setMedicare(double medicare) {
			this.medicare = medicare;
		}
		public double getMediare_amount() {
			return mediare_amount;
		}
		public void setMediare_amount(double mediare_amount) {
			this.mediare_amount = mediare_amount;
		}
		public double getProfit() {
			return profit;
		}
		public void setProfit(double profit) {
			this.profit = profit;
		}
		public double getProfit_amount() {
			return profit_amount;
		}
		public void setProfit_amount(double profit_amount) {
			this.profit_amount = profit_amount;
		}
		public double getCheckOff() {
			return checkOff;
		}
		public void setCheckOff(double checkOff) {
			this.checkOff = checkOff;
		}
		public double getInter() {
			return inter;
		}
		public void setInter(double inter) {
			this.inter = inter;
		}
		public double getReview() {
			return review;
		}
		public void setReview(double review) {
			this.review = review;
		}
		public double getReview_days() {
			return review_days;
		}
		public void setReview_days(int review_days) {
			this.review_days = review_days;
		}
		public double getOvertime() {
			return overtime;
		}
		public void setOvertime(double overtime) {
			this.overtime = overtime;
		}
		public double getOvertime_days() {
			return overtime_days;
		}
		public void setOvertime_days(int overtime_days) {
			this.overtime_days = overtime_days;
		}
		public double getBtrip() {
			return btrip;
		}
		public void setBtrip(double btrip) {
			this.btrip = btrip;
		}
		public double getBtriptax() {
			return btriptax;
		}
		public void setBtriptax(double btriptax) {
			this.btriptax = btriptax;
		}
		public double getCotch() {
			return cotch;
		}
		public void setCotch(double cotch) {
			this.cotch = cotch;
		}
		public double getCnalog() {
			return cnalog;
		}
		public void setCnalog(double cnalog) {
			this.cnalog = cnalog;
		}
		public double getRelief() {
			return relief;
		}
		public void setRelief(double relief) {
			this.relief = relief;
		}
		public double getTotaldeduction() {
			return totaldeduction;
		}
		public void setTotaldeduction(double totaldeduction) {
			this.totaldeduction = totaldeduction;
		}
		public double getAmount() {
			return amount;
		}
		public void setAmount(double amount) {
			this.amount = amount;
		}
		
	}	
	
}
